from datetime import datetime
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey, Table, cast, DATE
from sqlalchemy.orm import sessionmaker
from config import MYSQL_CONFIG_LOCAL as mysql_config

engine = create_engine(
    "mysql+pymysql://%s:%s@%s:3306/%s?charset=utf8" % (mysql_config["user"], mysql_config["password"],
                                                       mysql_config["host"], mysql_config["database"]),
    max_overflow=20,            # 超过连接池大小外最多创建的连接
    pool_size=10,               # 连接池大小
    pool_timeout=30,            # 池中没有线程最多等待的时间，否则报错
    pool_recycle=28800          # 多久之后对线程池中的线程进行一次连接的回收（重置）
)
DBSession = sessionmaker(bind=engine)

Base = declarative_base()


# 作者
class Author(Base):
    __tablename__ = "author"
    id = Column(Integer, autoincrement=True, primary_key=True, nullable=False)
    name = Column(String(100), default=None)                # 昵称
    head_img = Column(String(255), default=None)            # 头像
    phone = Column(String(100), default=None)               # 手机号
    email = Column(String(100), default=None)               # 邮箱
    password = Column(String(100), default=None)            # 密码
    bio = Column(String(500), default=None)                 # 个人说明
    is_delete = Column(Integer, nullable=False, default=0)  # 是否删除，0-否，1-是
    create_time = Column(DateTime, default=datetime.now)    # 创建时间
    last_login_time = Column(DateTime)                      # 上一次登录时间


# 文章
class Post(Base):
    __tablename__ = "post"
    id = Column(Integer, autoincrement=True, primary_key=True, nullable=False)
    title = Column(String(255), default=None)                   # 标题
    body = Column(String(1000), default=None)                    # 内容
    excerpt = Column(String(255), default=None)                 # 摘要
    is_delete = Column(Integer, nullable=False, default=0)      # 是否删除，0-否，1-是
    views = Column(Integer, nullable=False, default=0)          # 文章浏览量
    create_time = Column(DateTime, default=datetime.now)        # 创建时间
    modified_time = Column(DateTime)                            # 更改时间
    author_id = Column(Integer, ForeignKey("author.id"))


# 分类
class Category(Base):
    __tablename__ = "category"
    id = Column(Integer, autoincrement=True, primary_key=True, nullable=False)
    name = Column(String(100), nullable=False)                  # 名称
    remark = Column(String(255))                                # 备注
    is_delete = Column(Integer, nullable=False, default=0)      # 是否删除，0-否，1-是
    create_time = Column(DateTime, default=datetime.now)        # 创建时间
    author_id = Column(Integer, ForeignKey("author.id"))


# 文章-分类
class PostCateMiddle(Base):
    __tablename__ = "middle_post_category"
    id = Column(Integer, autoincrement=True, primary_key=True, nullable=False)
    post_id = Column(Integer, ForeignKey("post.id"))
    category_id = Column(Integer, ForeignKey("category.id"))
    create_time = Column(DateTime, default=datetime.now)        # 创建时间


# 标签
class Tag(Base):
    __tablename__ = "tag"
    id = Column(Integer, autoincrement=True, primary_key=True, nullable=False)
    name = Column(String(100), nullable=False)                  # 名称
    remark = Column(String(255))                                # 备注
    is_delete = Column(Integer, nullable=False, default=0)      # 是否删除，0-否，1-是
    create_time = Column(DateTime, default=datetime.now)        # 创建时间
    author_id = Column(Integer, ForeignKey("author.id"))


# 文章-标签
class PostTagMiddle(Base):
    __tablename__ = "middle_post_tag"
    id = Column(Integer, autoincrement=True, primary_key=True, nullable=False)
    post_id = Column(Integer, ForeignKey("post.id"))
    tag_id = Column(Integer, ForeignKey("tag.id"))
    create_time = Column(DateTime, default=datetime.now)        # 创建时间


# 访客
class Visitor(Base):
    __tablename__ = "visitor"
    id = Column(Integer, autoincrement=True, primary_key=True, nullable=False)
    nick_name = Column(String(50), default=None)                # 昵称
    email = Column(String(100), default=None)                   # 邮箱
    is_delete = Column(Integer, nullable=False, default=0)      # 是否删除，0-否，1-是
    create_time = Column(DateTime, default=datetime.now)        # 创建时间


# 评论
class Comment(Base):
    __tablename__ = "comment"
    id = Column(Integer, autoincrement=True, primary_key=True, nullable=False)
    visitor_id = Column(Integer, ForeignKey("visitor.id"))
    content = Column(String(255), default=None)                 # 内容
    is_delete = Column(Integer, nullable=False, default=0)      # 是否删除，0-否，1-是
    create_time = Column(DateTime, default=datetime.now)        # 创建时间
    post_id = Column(Integer, ForeignKey("post.id"))


# 文章更改记录
class PostUpdateHistory(Base):
    __tablename__ = "post_update_history"
    id = Column(Integer, autoincrement=True, primary_key=True, nullable=False)
    post_id = Column(Integer, ForeignKey("post.id"))
    change_content = Column(String(100), default=None)          # 更改内容
    create_time = Column(DateTime, default=datetime.now)        # 创建时间


# 文章浏览记录
class PostViewHistory(Base):
    __tablename__ = "post_view_history"
    id = Column(Integer, autoincrement=True, primary_key=True, nullable=False)
    nick_name = Column(String(50), default=None)                # 昵称
    ip_address = Column(String(50), default=None)               # IP地址
    post_id = Column(Integer, ForeignKey("post.id"))
    create_time = Column(DateTime, default=datetime.now)        # 创建时间


# 用户访问记录
class VisitorLogs(Base):
    __tablename__ = "visitor_logs"
    id = Column(Integer, autoincrement=True, primary_key=True, nullable=False)
    ip_address = Column(String(50), default=None)               # IP地址
    host = Column(String(50), default=None)                     # host地址
    uri = Column(String(100), default=None)                     # uri地址
    create_time = Column(DateTime, default=datetime.now)        # 创建时间


# 创建表
def create_db():
    print("start create database.....")

    # Base.metadata.drop_all(engine)
    print("drop all tables success!")

    Base.metadata.create_all(engine)
    print("created tables success!")


def create_author():
    session = DBSession()
    author = Author(name="Mason", phone="admin", password="123")
    session.add(author)
    session.commit()
    session.close()


def query_data():
    session = DBSession()
    obj = session.query(Post.title, Category.name)\
        .join(PostCateMiddle, Post.id==PostCateMiddle.post_id)\
        .join(Category, PostCateMiddle.category_id==Category.id)\
        .filter(Post.id==2).first()
    print(obj)
    count = session.query(Post)\
        .join(PostCateMiddle, Post.id==PostCateMiddle.post_id)\
        .join(Category, PostCateMiddle.category_id==Category.id)\
        .filter(Category.id==2).count()
    print(count)
    tag_names = session.query(Tag.name) \
        .join(PostTagMiddle, Tag.id == PostTagMiddle.tag_id) \
        .join(Post, PostTagMiddle.post_id == Post.id) \
        .filter(Post.id == 1).all()
    print(tag_names)
    session.close()


if __name__ == '__main__':
    create_db()
